package com.springBoot.springBootSysCore.modules.repository.system;

import com.springBoot.springBootSysCore.common.repository.IBaseRepository;
import com.springBoot.springBootSysCore.modules.entity.system.SysMessage;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * Created by timo on 2017/11/1.
 */
public interface SysMessageRepository extends IBaseRepository<SysMessage, String> {

    /**
     * 批量删除--假删除
     * @param asList
     */
    @Modifying
    @Query("update SysMessage set  delFlag = '1' where id in (?1)")
    public void updateDelFlag(List<String> asList);

    @Modifying
    @Query("delete from SysMessage where id in (?1)")
    public void deleteByIds(List<String> asList);




//    Both the following approaches work fine with MySQL for paginating native query.
//    They doesn't work with H2 though. It will complain the sql syntax error.
//    ORDER BY ?#{#pageable}
//    ORDER BY a.id \n#pageable\n

//    @Query(value = "select * from (select (@rowid\\:=@rowid+1) as RN, u.* from USERS u, (SELECT @rowid\\:=0) as init where  LASTNAME = ?1) as total"+
//            "where RN between ?#{#pageable.offset-1} and ?#{#pageable.offset + #pageable.pageSize}",
//            countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
//            nativeQuery = true)
//    Page<User> findByLastname(String lastname, Pageable pageable);

//    @Query(value = "select * from users where user_type in (:userTypes) and user_context='abc'--#pageable\n", nativeQuery = true)
//    List<User> getUsersByTypes(@Param("userTypes") List<String> userTypes, Pageable pageable);

    /**
     * 查询公告对应用户未读消息
     * @param userId
     * @return
     */
    @Query(value = "select m.id,m.title,m.describes,m.details,m.obj_id,m.create_date,ml.readed  from SYS_MESSAGE m  " +
            "left join sys_message_log ml on ml.message_id = m.id and  ml.user_id = ?1 " +
//            "where 1=1 \n#pageable\n",
            "where m.message_type ='2' and ml.del_flag = '0' \n#pageable\n",
           /* "where (ml.readed ='0' or ml.readed is null ) or ml.id is null  \n#pageable\n",*/
            /*countQuery = "select count(m.id) from SYS_MESSAGE m " +
                "left join sys_message_log ml on ml.message_id = m.id and  ml.user_id = ?1 " +
                "where (ml.readed ='0' or ml.readed is null ) or ml.id is null \n#pageable\n",*/nativeQuery = true)
    Page<Object[]> getPublicMessage(String userId,Pageable pageable);

    @Query(value = "select m.id from SYS_MESSAGE m  " +
            "left join sys_message_log ml on ml.message_id = m.id and  ml.user_id = ?1 " +
            "where  m.message_type ='2' and ml.del_flag = '0' and (ml.readed ='0' or ml.readed is null)",nativeQuery = true)
    List<String> getPublicMessage(String userId);

    @Query(value = "select count(m.id) from SYS_MESSAGE m  " +
            "left join sys_message_log ml on ml.message_id = m.id and  ml.user_id = ?1 " +
            "where  m.message_type ='2' and ml.del_flag = '0' and (ml.readed ='0' or ml.readed is null)",nativeQuery = true)
    int countPublicMessage(String userId);

    /**
     * 已读所以非公告消息
     * @param userId
     * @param publicType
     */
    @Query("update SysMessage set readed = true where messAgeType != ?2 and toSysUser.id =?1")
    @Modifying
    void readNotPublicMessage(String userId,String publicType);

    @Query("update SysMessage set readed = true where messAgeType = ?2 and toSysUser.id =?1")
    @Modifying
    void readMessageByType(String userId,String publicType);
}
